package com.pizza.database.mapping;

import java.io.Serializable;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.pizza.abstrait.AbstractAdresse;
import com.pizza.abstrait.AbstractClient;
import com.pizza.database.DBConnection;

import com.pizza.model.Adresse;
import com.pizza.model.Client;

/**
 * Class AdresseDB, mapping avec la bdd.
 *
 */
public class AdresseDB implements Serializable
{
	private DBConnection dbConnexion;

	public AdresseDB(DBConnection _dbConnexion)
	{
		dbConnexion = _dbConnexion;
	}

	public int insertAdresse(AbstractAdresse _adresse)
	{
		int retour = 0;
		Connection connexion = dbConnexion.getConnection();
		StringBuilder query = new StringBuilder();
		query.append("INSERT INTO adresses");
		query.append(" (adresse,cp,ville,telephone)");
		query.append(" VALUES(?,?,?,?)");

		try
		{
			//System.out.println("DEBUG: " + query.toString());
			PreparedStatement pStatement = connexion.prepareStatement(query.toString());

			pStatement.setString(1, _adresse.getAdresse());
			pStatement.setInt(2, _adresse.getCp());
			pStatement.setString(3, _adresse.getVille());
			pStatement.setString(4, _adresse.getTelephone());

			retour += pStatement.executeUpdate();

			ResultSet rs = pStatement.getGeneratedKeys();
			rs.next();
			_adresse.setId(rs.getInt(1));
			rs.close();

			pStatement.close();

		} catch (SQLException e)
		{
			e.printStackTrace();
		}
		return retour;
	}

	public int insertAdressesLivraison(AbstractClient _client)
	{
		int retour = 0;
		for (AbstractAdresse adresse : _client.getAdressesLivraison())
		{
			retour += insertAdresseLivraison(_client, adresse);
		}
		return retour;
	}

	public int deleteAdresse(AbstractAdresse _adresse)
	{
		int retour = 0;
		Connection connexion = dbConnexion.getConnection();
		StringBuilder query = new StringBuilder();
		query.append("DELETE FROM adresses");
		query.append(" WHERE id=?");
		try
		{
			//System.out.println("DEBUG: " + query.toString());
			PreparedStatement pStatement = connexion.prepareStatement(query.toString());
			pStatement.setInt(1, _adresse.getId());
			retour += pStatement.executeUpdate();
			pStatement.close();

		} catch (SQLException e)
		{
			e.printStackTrace();
		}
		return retour;
	}

	public int removeAdresseLivraison(AbstractClient _client, AbstractAdresse _adresse)
	{
		int retour = 0;
		Connection connexion = dbConnexion.getConnection();
		StringBuilder query = new StringBuilder();
		query.append("DELETE FROM clients_adresseslivraison");
		query.append(" WHERE id_clients=?");
		query.append(" AND id_adresses=?");
		try
		{
			deleteAdresse(_adresse);
			
			//System.out.println("DEBUG: " + query.toString());
			PreparedStatement pStatement = connexion.prepareStatement(query.toString());
			pStatement.setInt(1, _client.getId());
			pStatement.setInt(2, _adresse.getId());
			retour += pStatement.executeUpdate();
			pStatement.close();
		} catch (SQLException e)
		{
			e.printStackTrace();
		}
		return retour;
	}

	public int insertAdresseLivraison(AbstractClient _client, AbstractAdresse _adresse)
	{
		int retour = 0;
		Connection connexion = dbConnexion.getConnection();
		StringBuilder query = new StringBuilder();
		query.append("INSERT INTO clients_adresseslivraison");
		query.append(" (id_clients,id_adresses)");
		query.append(" VALUES(?,?)");

		try
		{
			if (!_client.getAdresseFacturation().equals(_adresse))
				insertAdresse(_adresse);

			//System.out.println("DEBUG: " + query.toString());
			PreparedStatement pStatement = connexion.prepareStatement(query.toString());

			pStatement.setInt(1, _client.getId());
			pStatement.setInt(2, _adresse.getId());

			retour += pStatement.executeUpdate();

			pStatement.close();

		} catch (SQLException e)
		{
			e.printStackTrace();
		}
		return retour;
	}

	public List<AbstractAdresse> getListAdressesLivraison(AbstractClient _client)
	{
		List<AbstractAdresse> retour = new ArrayList<AbstractAdresse>();
		Connection connexion = dbConnexion.getConnection();
		StringBuilder query = new StringBuilder();
		query.append("SELECT id_adresses");
		query.append(" FROM clients_adresseslivraison");
		query.append(" WHERE id_clients=?");
		try
		{
			//System.out.println("DEBUG: " + query.toString());
			PreparedStatement pStatement = connexion.prepareStatement(query.toString());
			pStatement.setInt(1, _client.getId());
			ResultSet rs = pStatement.executeQuery();
			while (rs.next())
			{
				retour.add(getAdresseById(rs.getInt("id_adresses")));
			}
			rs.close();
			pStatement.close();
		} catch (SQLException e)
		{
			e.printStackTrace();
		}
		return retour;
	}

	public Adresse getAdresseById(int _id)
	{
		Adresse retour = null;
		Connection connexion = dbConnexion.getConnection();
		StringBuilder query = new StringBuilder();
		query.append("SELECT adresse,cp,ville,telephone");
		query.append(" FROM adresses");
		query.append(" WHERE id=?");
		try
		{
			//System.out.println("DEBUG: " + query.toString());
			PreparedStatement pStatement = connexion.prepareStatement(query.toString());
			pStatement.setInt(1, _id);
			ResultSet rs = pStatement.executeQuery();
			if (rs.first())
			{
				String adresse = rs.getString("adresse");
				int cp = rs.getInt("cp");
				String ville = rs.getString("ville");
				String telephone = rs.getString("telephone");

				retour = new Adresse(_id, adresse, cp, ville, telephone);
			}
			rs.close();
			pStatement.close();
		} catch (SQLException e)
		{
			e.printStackTrace();
		}
		return retour;
	}

	public Client getClientFromAdresseId()
	{
		return null;
	}

	public int updateAdresse(AbstractAdresse _adresse)
	{
		int retour = 0;
		Connection connexion = dbConnexion.getConnection();
		StringBuilder queryupdate = new StringBuilder();
		StringBuilder queryselect = new StringBuilder();

		queryselect.append("SELECT * FROM adresses");
		queryselect.append(" WHERE id=?");
		try
		{
			PreparedStatement pStatement = connexion.prepareStatement(queryselect
					.toString());
			pStatement.setInt(1, _adresse.getId());
			ResultSet rs = pStatement.executeQuery();
			rs.first();

			queryupdate.append("UPDATE adresses SET");

			if (!rs.getString("adresse").equals(_adresse.getAdresse()))
				queryupdate.append(" adresse='" + _adresse.getAdresse() + "',");
			if (!rs.getString("cp").equals(_adresse.getCp()))
				queryupdate.append(" cp=" + _adresse.getCp() + ",");
			if (!rs.getString("ville").equals(_adresse.getVille()))
				queryupdate.append(" ville='" + _adresse.getVille() + "',");
			if (!rs.getString("telephone").equals(_adresse.getTelephone()))
				queryupdate.append(" telephone='" + _adresse.getTelephone() + "',");

			if (queryupdate.charAt(queryupdate.length() - 1) == ',')
				queryupdate.deleteCharAt(queryupdate.length() - 1);
			queryupdate.append(" WHERE id=" + _adresse.getId());

			//System.out.println("DEBUG: " + queryupdate);
			retour = connexion.createStatement().executeUpdate(queryupdate.toString());
		} catch (SQLException e)
		{
			e.printStackTrace();
		}
		return retour;
	}

}
